﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;

namespace TestSqlite
{
    class Program
    {
        static void Main(string[] args)
        {
            

            Console.ReadKey();
        }


        // 查询 info 表中的数据
        static void test_query_table(string tableName) {
            string sql = $"select * from {tableName}";
            SQLiteConnection conn = new SQLiteConnection(@"Data Source=E:\my_projects\my_fil.db;Version=3;");
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
            DataTable dt = new DataTable(tableName);
            adapter.Fill(dt);

            int idx = 0;
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine($"{idx++}========================");
                foreach (DataColumn column in dt.Columns)
                {
                    Console.WriteLine($"{dt.TableName}  {column.ColumnName} {row[column]}");
                }
            }
        }

        /// <summary>
        /// 创建一个空的数据库
        /// </summary>
        /// <param name="dbName">如: E:\data\dbname.sqlite</param>
        static void createNewDatabase(string dbName)
        {
            SQLiteConnection.CreateFile(dbName);
        }

        /// <summary>
        /// 连接到指定的数据库
        /// 连接字符串的一些设置: https://www.connectionstrings.com/sqlite-net-provider/
        /// Basic: Data Source=c:\mydb.db;Version=3;  (当前使用的就是此种形式) 不支持 Version 2
        /// In-Memory Database: Data Source=:memory:;Version=3;New=True;
        /// Using UTF16:  Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True;
        /// With password:  Data Source=c:\mydb.db;Version=3;Password=myPassword;
        /// Using the pre 3.3x database format: Data Source=c:\mydb.db;Version=3;Legacy Format=True;
        /// With connection pooling: Data Source=c:\mydb.db;Version=3;Pooling=True;Max Pool Size=100;
        /// Read only connection:  Data Source=c:\mydb.db;Version=3;Read Only=True;
        /// Using DateTime.Ticks as datetime format: Data Source=c:\mydb.db;Version=3;DateTimeFormat=Ticks;
        /// Store GUID as text:  Data Source=c:\mydb.db;Version=3;BinaryGUID=False;
        /// Specify cache size: Data Source=c:\mydb.db;Version=3;Cache Size=2000;
        /// Specify page size: Data Source=c:\mydb.db;Version=3;Page Size=1024;
        /// Disable enlistment in distributed transactions: Data Source=c:\mydb.db;Version=3;Enlist=N;
        /// Disable create database behaviour: Data Source=c:\mydb.db;Version=3;FailIfMissing=True;
        /// Limit the size of database: Data Source=c:\mydb.db;Version=3;Max Page Count=5000;
        /// Disable the Journal File:  Data Source=c:\mydb.db;Version=3;Journal Mode=Off;
        /// Persist the Journal File:  Data Source=c:\mydb.db;Version=3;Journal Mode=Persist;
        /// Controling file flushing:  Data Source=c:\mydb.db;Version=3;Synchronous=Full;
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        static SQLiteConnection connectToDatabase(string dbName)
        {
            SQLiteConnection conn = new SQLiteConnection($"Data Source={dbName};Version=3;");
            conn.Open();
            return conn;
        }

        /// <summary>
        ///  创建一个table
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql">如: create table user (id int primary key autoincrement,name text,age int)</param>
        static void createTable(SQLiteConnection conn,string sql)
        {
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        static void insertData(SQLiteConnection conn,string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            cmd.ExecuteNonQuery();
        }


        static DataTable queryData(SQLiteConnection conn,string sql)
        {
            /**
             *  或者使用这样的方式去做查询
             *  string sql = "select * from highscores order by score desc";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                    Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
                Console.ReadLine();
             **/

            SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
    }
}
